﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Data;
namespace Dack_Web2
{

    public class Sql_DataProvider
    {
        public static OleDbConnection SQLConnect()
        {
         
            string connect = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=" +
                              HttpContext.Current.Server.MapPath("~/Data/DealWeb2.mdb");
            OleDbConnection Cn = new OleDbConnection(connect);
            Cn.Open();
            return Cn;

        }        

        //------------------------------------------------------- Return table ------------------------
        public static DataTable SQL_ExecuteTable(string sql)
        {
            OleDbConnection Connect = new OleDbConnection();
            Connect = SQLConnect();
            OleDbDataAdapter da = new OleDbDataAdapter(sql, Connect);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static DataTable SQL_ExecuteTable(OleDbCommand cmd, OleDbParameter[] prs)
        {
            for (int i = 0; i < prs.Length; i++)
            {
                cmd.Parameters.Add(prs[i]);
            }
            OleDbConnection Connect = new OleDbConnection();
            Connect = SQLConnect();
            cmd.Connection = Connect;
            if ((Connect.State != ConnectionState.Open))
            {
                cmd.Connection.Open();
            }
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            Connect.Close();
            return dt;
        }
        //-----------------------------------------------------------------------------------------------

        public static OleDbDataReader SQL_ExcuteQueryReader(string sql)
        {
            OleDbConnection Connect = new OleDbConnection();
            Connect = SQLConnect();
            OleDbCommand cmd = new OleDbCommand(sql, Connect);
            try
            {
                if ((Connect.State != ConnectionState.Open))
                {
                    cmd.Connection.Open();
                }
                OleDbDataReader Reader = cmd.ExecuteReader();
                return Reader;
            }
            catch
            {
                Connect.Close();
                return null;
            }
        }

        //--------------------------------------------------------------------------------------------------
        public static Boolean SQL_ExecuteNonQuery(string ProcName, OleDbParameter[] prs)
        {
            OleDbConnection Connect = new OleDbConnection();
            Connect = SQLConnect();
            try
            {
               
                OleDbCommand cmd = new OleDbCommand(ProcName);
                for (int i = 0; i < prs.Length ; i++)
                {
                    cmd.Parameters.Add(prs[i]);
                }
                cmd.Connection = Connect;
                if ((Connect.State != ConnectionState.Open))
                {
                    cmd.Connection.Open();
                }
                cmd.ExecuteNonQuery();
                Connect.Close();
                return true;
            }
            catch
            {
                Connect.Close();
                return false;
            }
        }

        public static bool SQL_ExecuteNonQuery(string sql)
        {
            OleDbConnection Connect = new OleDbConnection();
            Connect = SQLConnect();
            try
            {
                
                OleDbCommand cmd = new OleDbCommand(sql, Connect);
                if ((Connect.State != ConnectionState.Open))
                {
                    cmd.Connection.Open();
                }
                cmd.ExecuteNonQuery();
                Connect.Close();
                return true;
            }
            catch
            {
                Connect.Close();
                return false;
            }
        }

        //Phan Trang ---------------------------------------------------------------------------------------
        public static DataTable SQL_ExecuteNonQuery(string Table, int starpage, int endpage)
        {
            OleDbConnection Connect = new OleDbConnection();
            Connect = SQLConnect();
            string sql = string.Format("select * from {0} Offset  {1}  Rows Fetch Next {2} Rows Only;", Table, starpage, endpage);
            OleDbDataAdapter da = new OleDbDataAdapter(sql, Connect);
            DataTable dt = new DataTable();
            da.Fill(dt);
            Connect.Close();

            return dt;
        }
        //--------------------------------------------------------------------------------------------------
        // <summary>
        // Reaturn Scalar ------------------------
        // </summary>
        // <param name="sql"></param>
        // <returns></returns>
        // 

        public static object SQL_ExecuteScalar(string sql)
        {
            OleDbConnection Connect = new OleDbConnection();
            Connect = SQLConnect();
            try
            {
                OleDbCommand cmd = new OleDbCommand(sql, Connect);
                if ((Connect.State != ConnectionState.Open))
                {
                    cmd.Connection.Open();
                }
                object Obj = cmd.ExecuteScalar();
                Connect.Close();

                return Obj;
            }
            catch{ Connect.Close();}
            return null;
        }

        public static int SQL_ExecuteScalar(OleDbCommand cmd)
        {
            OleDbConnection Connect = new OleDbConnection();
            Connect = SQLConnect();
            try
            {
                if ((Connect.State == ConnectionState.Closed))
                {
                    Connect.Open();
                }
                cmd.Connection = Connect;
                int Obj = int.Parse(cmd.ExecuteScalar().ToString());
                Connect.Close();
                return Obj;
            }
            catch { }
            return -1;
        }
    }
}
